﻿Imports System.Data
Imports Sybase.Data.AseClient

Imports System.IO
Imports System.Xml

Imports System.Collections.ObjectModel


Public Class CServiciosMacrosSyBaseDB

    Public Shared USUARIO          As String
    Public Shared IDUSUARIO        As Integer    
    Public Shared NOMBREUSUARIO    As String
    Public Shared SESION           As Integer
    Public Shared USUARIORED       As String 
    
    'PARA GESTIÓN POR ESTRATEGIAS
    Public Const GxEPadre As String = "C"
    Public Const GxEHijo As String = "Y"
    
  
    Public Enum OPCIONFUENTEXMLREPOFL 
       CODIGO_SISTEMA = 0
       BASE_DATOS     = 1
    End Enum



  #Region "LIQUIDEZ-PORVENIR-SALDOS"

  
    Public Shared Function LayoutLiquidezXML(ByRef eventoBit As CSBitEvento) As DataSet
        Dim dt As New DataSet()


        'Eliminar esta Validacion cuando se deseche la vieja funcionalidad, antes del Flujo de Liquidez
        If eventoBit is Nothing then 
           Return Nothing 
        End If

        Try
           
           Dim lstBitDet  = new List(Of CTBitEventoDet )
           
           Dim opcion As OPCIONFUENTEXMLREPOFL = CServicioLocalSQLServerDB.ObtenerSwitchFuenteXMLParaFLRepo  ()
           lstBitDet.Add (New CTBitEventoDet ("Opción",opcion))  


            Dim path As String
            Select  case opcion 

               Case OPCIONFUENTEXMLREPOFL.BASE_DATOS 
                   path  = CServicioLocalSQLServerDB.ObtenerRutaDefXMLParaReposEnFL () 
                   lstBitDet.Add (New CTBitEventoDet ("Ruta",path))
                   dt.ReadXml(path) 
               Case Else                  
                 path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)                                      
                 path = path.Replace("file:\","")

                 lstBitDet.Add (New CTBitEventoDet ("Ruta",path & "/Datos/dbXML/LayoutLiquidez.xml"))
                 dt.ReadXml(path & "/Datos/dbXML/LayoutLiquidez.xml")
            End Select
       
            eventoBit.GuardarDetalles (lstBitDet)


        Catch ex As Exception
            Throw ex
        End Try

        Return dt
    End Function

    Public Shared Function FondosSalLiquidezDB(ByRef lstFondos As List(Of CTFondoXIntermediario)) As DataTable

        Dim strFondoSalDiso As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtFondoSalDiso As New DataTable()

        Dim idRegBit As Integer


        strFondoSalDiso = " select RTRIM( p.PF_COD) as fondo, ROUnd(p.AMT_CURRENT,2) as saldo " & _
                          " from ID_POS_CASH p noholdlock, PORTFOLIO f noholdlock " & _
                          " where  f.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ") and f.PF_COD = p.PF_COD " & _
                          " and p.SETTLE_DAT  =  CONVERT (DATE,GETDATE()) " & _
                          " and p.CUR_COD ='MXN' " & _
                          " and (p.AMT_AFTER_XACT_DAT <> 0 or p.AMT_CURRENT<>0) and (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null) order by p.PF_COD"

        idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "FondosSalLiquidezDB", "Consulta saldos: " & strFondoSalDiso)

        dtFondoSalDiso = dbCnn.EjecutarConsulta(strFondoSalDiso, pm, sMsgError)

        CUtileria.CompletarRegBitacora(idRegBit)


        If Not sMsgError.Equals("") Then
            idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "FondosSalLiquidezDB", sMsgError)
            CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If

        Return dtFondoSalDiso

    End Function

    Public Shared Function OrdenesPendLiquidezDB(ByRef lstFondos As List(Of CTFondoXIntermediario)) As DataTable

        Dim strOrdenesPend As String = ""
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)


        Dim dtOrdenesPend As New DataTable()

        Dim idRegBit As Integer


        strOrdenesPend = " select RTRIM( b.PF_COD) as PFCOD,round(SUM(AMT_REQUESTED),2) as ORDREPO from ID_ORDER a noholdlock , ID_ORDR_PF b noholdlock,  PORTFOLIO f noholdlock" & _
                         " where a.ORDR_NUM=b.ORDR_NUM and  f.PF_COD = b.PF_COD    and (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null) " & _
                         " AND a.XACT_TYP in ('CRE') AND a.BUS_STATE='ORS' and b.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ")  " & _
                         " group by b.PF_COD "


        idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "OrdenesPendLiquidezDB", "Consulta ordenes-pending: " & strOrdenesPend)
        dtOrdenesPend = dbCnn.EjecutarConsulta(strOrdenesPend, pm, sMsgError)
        CUtileria.CompletarRegBitacora(idRegBit)



        If Not sMsgError.Equals("") Then
            idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "OrdenesPendLiquidezDB", sMsgError)
            CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If


        Return dtOrdenesPend

    End Function

    Public Shared Function PendingCashAndAjustDB(ByRef lstFondos As List(Of CTFondoXIntermediario)) As DataTable

        Dim strPendingCashAndAjust As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)


        Dim dtPendingCashAndAjust As New DataTable()

        Dim idRegBit As Integer




        strPendingCashAndAjust = "SELECT  a.PF_COD, a.XACT_TYP," & _
                                " case a.XACT_TYP " & _
                                "       when   'PE-' then round(sum(a.INST_QTY *-1),2) " & _
                                "       when   'PE+' then round(sum(a.INST_QTY ),2) " & _
                                " end  AS SALDOSPLUSMINUS " & _
                                " FROM ID_XACT a noholdlock inner join PORTFOLIO f noholdlock on (f.PF_COD = a.PF_COD)   " & _
                                " WHERE  " & _
                                " (a.XACT_TYP IN ('PE-')  or a.XACT_TYP IN ('PE+')) " & _
                                " AND a.XACT_DAT=CONVERT (DATE,GETDATE())         " & _
                                " AND a.BUS_STATE='OP'                            " & _
                                " AND  a.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ") " & _
                                " AND (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null)" & _
                                " GROUP by a.PF_COD, a.XACT_TYP "



        idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "PendingCashAndAjustDB", "Consulta pending-cash-ajust: " & strPendingCashAndAjust)
        dtPendingCashAndAjust = dbCnn.EjecutarConsulta(strPendingCashAndAjust, pm, sMsgError)
        CUtileria.CompletarRegBitacora(idRegBit)


        If Not sMsgError.Equals("") Then
            idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "PendingCashAndAjustDB", sMsgError)
            CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If

        Return dtPendingCashAndAjust


    End Function

    Private Shared Function GenerarCondicionFondos(ByRef lstFondos As List(Of CTFondoXIntermediario)) As String

        Dim fondos As New System.Text.StringBuilder
        Try

            For Each fondo As CTFondoXIntermediario  In lstFondos
                fondos.Append("'" & fondo.Fondo.Trim & "',")
            Next

            fondos.Remove(fondos.Length - 1, 1)
            Dim aux As String = fondos.ToString()
            Return aux
        Catch ex As Exception
            Return ""
        End Try


    End Function



    Public Shared Function FondosSalLiquidezDB(ByRef lstFondos As List(Of string)) As DataTable

        Dim strFondoSalDiso As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtFondoSalDiso As New DataTable()



        strFondoSalDiso = " select RTRIM( p.PF_COD) as fondo, ROUnd(p.AMT_CURRENT,2) as saldo " & _
                          " from ID_POS_CASH p noholdlock, PORTFOLIO f noholdlock " & _
                          " where  f.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ") and f.PF_COD = p.PF_COD " & _
                          " and p.SETTLE_DAT  =  CONVERT (DATE,GETDATE()) " & _
                          " and p.CUR_COD ='MXN' " & _
                          " and (p.AMT_AFTER_XACT_DAT <> 0 or p.AMT_CURRENT<>0) and (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null) order by p.PF_COD"
      
        dtFondoSalDiso = dbCnn.EjecutarConsulta(strFondoSalDiso, pm, sMsgError)

        
        If Not sMsgError.Equals("") Then                      
            Throw New Exception(sMsgError)
        End If

        Return dtFondoSalDiso

    End Function

    Public Shared Function OrdenesPendLiquidezDB(ByRef lstFondos As List(Of string)) As DataTable

        Dim strOrdenesPend As String = ""
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)


        Dim dtOrdenesPend As New DataTable()

        Dim idRegBit As Integer


        strOrdenesPend = " select RTRIM( b.PF_COD) as PFCOD,round(SUM(AMT_REQUESTED),2) as ORDREPO from ID_ORDER a noholdlock , ID_ORDR_PF b noholdlock,  PORTFOLIO f noholdlock" & _
                         " where a.ORDR_NUM=b.ORDR_NUM and  f.PF_COD = b.PF_COD    and (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null) " & _
                         " AND a.XACT_TYP in ('CRE') AND a.BUS_STATE='ORS' and b.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ")  " & _
                         " group by b.PF_COD "


        idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "OrdenesPendLiquidezDB", "Consulta ordenes-pending: " & strOrdenesPend)
        dtOrdenesPend = dbCnn.EjecutarConsulta(strOrdenesPend, pm, sMsgError)
        CUtileria.CompletarRegBitacora(idRegBit)



        If Not sMsgError.Equals("") Then
            idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "OrdenesPendLiquidezDB", sMsgError)
            CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If


        Return dtOrdenesPend

    End Function

    Public Shared Function PendingCashAndAjustDB(ByRef lstFondos As List(Of String)) As DataTable

        Dim strPendingCashAndAjust As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)


        Dim dtPendingCashAndAjust As New DataTable()

        Dim idRegBit As Integer




        strPendingCashAndAjust = "SELECT  a.PF_COD, a.XACT_TYP," & _
                                " case a.XACT_TYP " & _
                                "       when   'PE-' then round(sum(a.INST_QTY *-1),2) " & _
                                "       when   'PE+' then round(sum(a.INST_QTY ),2) " & _
                                " end  AS SALDOSPLUSMINUS " & _
                                " FROM ID_XACT a noholdlock inner join PORTFOLIO f noholdlock on (f.PF_COD = a.PF_COD)   " & _
                                " WHERE  " & _
                                " (a.XACT_TYP IN ('PE-')  or a.XACT_TYP IN ('PE+')) " & _
                                " AND a.XACT_DAT=CONVERT (DATE,GETDATE())         " & _
                                " AND a.BUS_STATE='OP'                            " & _
                                " AND  a.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ") " & _
                                " AND (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null)" & _
                                " GROUP by a.PF_COD, a.XACT_TYP "



        idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "PendingCashAndAjustDB", "Consulta pending-cash-ajust: " & strPendingCashAndAjust)
        dtPendingCashAndAjust = dbCnn.EjecutarConsulta(strPendingCashAndAjust, pm, sMsgError)
        CUtileria.CompletarRegBitacora(idRegBit)


        If Not sMsgError.Equals("") Then
            idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "PendingCashAndAjustDB", sMsgError)
            CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If

        Return dtPendingCashAndAjust


    End Function

    Private Shared Function GenerarCondicionFondos(ByRef lstFondos As List(Of String)) As String

        Dim fondos As New System.Text.StringBuilder
        Try

            For Each fondo As string  In lstFondos
                fondos.Append("'" & fondo.Trim & "',")
            Next

            fondos.Remove(fondos.Length - 1, 1)
            Dim aux As String = fondos.ToString()
            Return aux
        Catch ex As Exception
            Return ""
        End Try


    End Function



    



 #End Region

  #Region "SEGURIDAD"
    
    Public Shared Function ValidarAccesoUsuario(ByVal usuario As String, ByVal password As String) As Boolean

        Dim strUsuarioPwd As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtUsuarioPwd As New DataTable()


        strUsuarioPwd = "  SELECT * FROM ID_USR  WHERE USR_NAM = '" & usuario & "' AND USR_PASSWORD= '" & password & "' "

        dtUsuarioPwd = dbCnn.EjecutarConsulta(strUsuarioPwd, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        If dtUsuarioPwd.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ValidarExistenciaUsuarioDECALOG(ByVal usuario As String) As DataTable

        Dim strUsuarioPwd As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtUsuarioPwd As New DataTable()


        strUsuarioPwd = "SELECT USR_NAM,USR_FULL_NAME FROM ID_USR  WHERE USR_NAM = '" & usuario & "'"

        dtUsuarioPwd = dbCnn.EjecutarConsulta(strUsuarioPwd, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtUsuarioPwd        
    End Function

#End Region

  #Region "GENERALES"

    Public Shared Function FechaHoyDB() As Date

        Dim strFechaSistema As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtFechaHoy As New DataTable()


        strFechaSistema = " select getdate() as fecha "


        dtFechaHoy = dbCnn.EjecutarConsulta(strFechaSistema, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If


        Return CType(dtFechaHoy.Rows(0)("fecha"), Date)

    End Function
 #End Region

  #Region "COVAFTL"


    Public Shared Function ObtieneInstrumentos() As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT LTRIM(RTRIM(INST_ACR)) INST_ACR,INST_TYP,INST_SH_NAM,RECV_FREQ, INST_NUM, INST_ISIN_COD, STR_REPLACE(LTRIM(RTRIM(INST_SH_NAM)),'_',NULL)  INST_SH_NAM_MOD FROM INSTRUMENT noholdlock WHERE STATUS<>'F'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtienePortfolios() As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT LTRIM(RTRIM(PF_COD)) PF_COD,PF_ACR FROM PORTFOLIO WHERE PF_C_FREE15 = '" & GxEPadre & "' OR PF_C_FREE15 IS NULL" 'Se modifica para gestión por estrategias

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneBrokers() As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT RTRIM(LTRIM(FAX_NUM)) FAX_NUM,RTRIM(LTRIM(BROKER_COD)) BROKER_COD FROM ID_BROKER WHERE TYPE_='I' AND BROKER_VALIDITY='O'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneTransaccionesABorrar(ByVal strFecha As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT 'ID_DELETE_XACT' ENTITE,XACT_NUM FROM ID_XACT WHERE REC_CRE_DAT>='" + strFecha + "' AND BUS_STATE='OP' AND XACT_TYP NOT IN ('PRC','PEC','PRV','PRE','PEV','PCC','PE-','PE+','ROB','VRP')"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function ObtieneChequerasABorrar(ByVal strFecha As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT 'ID_DELETE_CHANGE' ENTITE,CONT_NUM FROM ID_CONT WHERE  REC_CRE_DAT>='" + strFecha + "' AND STATUS<>'D' AND CONT_FOREX_TYP='SPOT'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

 #End Region

  #Region "REPORTING"

    Public Shared Function ObtieneJerarquias(ByVal strNombreJerarquia As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT HIER_NOD,HIER_CONDITION FROM ID_HIER_NODE noholdlock WHERE HIER_VIEW= '" + strNombreJerarquia + "' AND HIER_SONS_NB=0 AND HIER_CONDITION IS NOT NULL AND LEN(HIER_CONDITION) > 0"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function QueryCASHMXN(ByVal strFondos As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        'Se hicieron cambios para gestión por estrategias

        Dim strConsulta As String = " select 'CHEQUERA MXN' as NODO,   RTRIM( p.PF_COD) FONDO," & _
        " p.AMT_AFTER_XACT_DAT CONTRIBUCION,p.CUR_COD NOMBRE_INSTRUMENTO," & _
        " p.CUR_COD ACRONIMO_INSTRUMENTO, p.CUR_COD CODIGO_INSTRUMENTO from ID_POS_CASH p noholdlock, PORTFOLIO f noholdlock " & _
        " where  f.PF_COD in(" & strFondos & ") and f.PF_COD = p.PF_COD " & _
        " and p.SETTLE_DAT  =  CONVERT (DATE,GETDATE()) " & _
        " and p.CUR_COD='MXN' " & _
       " and (p.AMT_AFTER_XACT_DAT <> 0 or p.AMT_CURRENT<>0) and (f.PF_C_FREE15 = '" & GxEPadre & "' OR f.PF_C_FREE15 IS NULL)" & _
       " UNION " & _
        " select 'CHEQUERA USD' as NODO,   RTRIM( p.PF_COD) FONDO, p.AMT_AFTER_XACT_DAT * c.CUR_RATE CONTRIBUCION,p.CUR_COD NOMBRE_INSTRUMENTO, p.CUR_COD ACRONIMO_INSTRUMENTO, p.CUR_COD CODIGO_INSTRUMENTO from " & _
        " ID_POS_CASH p noholdlock, PORTFOLIO f noholdlock, ID_CUR_QUOTE c noholdlock where  f.PF_COD in (" & strFondos & ") and f.PF_COD = p.PF_COD  and " & _
         " p.SETTLE_DAT  =  CONVERT (DATE,GETDATE())  and c.CUR_COD=p.CUR_COD and " & _
          " p.CUR_COD='USD'  and (p.AMT_AFTER_XACT_DAT <> 0 or p.AMT_CURRENT<>0) and (f.PF_C_FREE15 = '" & GxEPadre & "' OR f.PF_C_FREE15 IS NULL) "

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function QueryJerarquias(ByVal strFondos As String, ByVal strNombreJerarquia As String, ByVal strCondicionJerarquia As String) As DataTable
        Dim strCondicion As String = "(SELECT INST_NUM from IDPERF_DBREL noholdlock WHERE " & strCondicionJerarquia.Trim & "))"
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " select '" & strNombreJerarquia & "' AS NODO," & _
                "   RTRIM( p.PF_COD) FONDO,p.POS_QTY * a.INSTD_F_FREE5 AS CONTRIBUCION,v.INST_SH_NAM , v.INST_ACR, v.INST_COD, v.INST_ISIN_COD, v.INST_TYP, v.QUOTE_MOD, c.QUOTE_CLOSE, c.QUOTE_DAT, a.SENSITIVITY, a.BETA, t.POS_TYP, a.CONVEXITY, a.DELTA,a.DURATION,a.YTM,a.VARIATION,a.AVG_LIFE," & _
                " a.VOLATILITY,a.CORR_MKT," & _
                " a.INSTD_C_FREE1,a.INSTD_C_FREE8," & _
                " a.INSTD_F_FREE1,a.INSTD_F_FREE2,a.INSTD_F_FREE3,a.INSTD_F_FREE4," & _
                " a.INSTD_F_FREE5, a.INSTD_F_FREE6," & _
                " a.INSTD_F_FREE7, a.INSTD_F_FREE8," & _
                " a.INSTD_F_FREE9,v.NOMINAL_AMT,v.INST_NUM,p.POS_QTY" & _
                " from PORTFOLIO f noholdlock, ID_POS_PROXY p noholdlock, INSTRUMENT v noholdlock, ID_INST_QUOTE " & _
                " c noholdlock, ID_UCP_PROXY u noholdlock, ID_POS_TYP t noholdlock ,ID_INST_DATA a noholdlock where f.PF_COD in (" & strFondos & ") and (f.PF_C_FREE15 = '" & GxEPadre &"' OR f.PF_C_FREE15 IS NULL) and f.PF_COD = p.PF_COD " & _
                " and  p.INST_NUM  = v.INST_NUM  and v.INST_NUM in" & _
                "(select r.INST_NUM from IDPERF_DBREL r noholdlock  where r.INST_NUM in" & _
              strCondicion & _
                 " and p.POS_TYP  = t.POS_TYP " & _
                    "and p.POS_QTY  != 0 " & _
                     " and v.INST_NUM  = c.INST_NUM " & _
                     " and v.MKT_LOC_COD_1 = c.MKT_LOC_COD " & _
                     " and u.POS_GRP    = t.POS_GRP " & _
                     " and p.PF_COD     = u.PF_COD " & _
                     " and p.INST_NUM   = u.INST_NUM " & _
                     " and p.SUB_PF_COD = u.SUB_PF_COD " & _
             " and a.INST_NUM = v.INST_NUM " & _
             " and v.STATUS!= 'F' ORDER BY v.INST_SH_NAM"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

    Public Shared Function QueryInstrumentoReporting(ByVal strInstrumento As String) As DataTable        
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT A.INST_NAM,A.NOMINAL_AMT,B.QUOTE_CLOSE, A.INST_NUM, " & _
        " C.AVG_LIFE , C.DELTA, A.INST_TYP " & _
        " FROM INSTRUMENT A,ID_INST_QUOTE B, ID_INST_DATA C WHERE " & _
        " A.INST_NUM=B.INST_NUM AND C.INST_NUM=B.INST_NUM AND A.STATUS<>'F' " & _
        " AND INST_SH_NAM ='" & strInstrumento & "'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

#End Region

  #Region "VALMER"
    
            #Region "VALMER LINEA"
        
            Public Shared Function ObtienerInstrumentosValmer(ByVal instrumento As String) As DataTable

        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT INST_SH_NAM,INST_NUM FROM INSTRUMENT  WHERE INST_SH_NAM LIKE '" & instrumento & "%' ORDER BY INST_SH_NAM "

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function
        
            Public Shared Function ObtienerDatosCheck(ByVal numInstrumento As Integer) As DataTable

        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""
        Dim strConsulta As String = "SELECT NOMINAL_RATE,RATE_TYP,RECV_IDX_COD,RECV_BASIS_FIXED,RECV_FREQ,RECV_MARGIN_ADD  FROM INSTRUMENT WHERE INST_NUM=" & numInstrumento

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function
        
            Public Shared Function ObtienerDatosEvt(ByVal numInstrumento As Integer) As DataTable

        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT EVT_NUM FROM ID_EVT WHERE ID_EVT.INST_CONT_NUM=" & _
                                     numInstrumento & " AND ID_EVT.XACTC_TYP in ('COUPAY','AMORNOM','BONDRED') " & _
                                     " AND EVT_NAT='A' AND EVT_DAT > getdate() "


        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function
        
        #End Region
                    
            #Region "VALMER BATCH"  

          Public Shared Function ObtienerSecTypesXGrupo(ByVal grupos As String) As DataTable

        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT distinct A.INST_TYP as TIPO "
                     strConsulta &= " FROM ID_INST_TYP A,   INSTRUMENT B  "
                     strConsulta &= " WHERE A.INST_TYP=B.INST_TYP AND A.INST_TYP IN (SELECT INST_TYP FROM ID_INST_TYP WHERE INST_GRP_COD IN ( " & grupos & "))"
                     strConsulta &= " AND B.STATUS<>'F' "
        

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function

          Public Shared Function ObtienerInstrumentosBatchValmerSecType(ByVal secType As String) As DataTable

        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT A.INST_GRP_COD, A.INST_TYP, B.INST_NUM, B.INST_SH_NAM  "
                     strConsulta &= " FROM ID_INST_TYP A,   INSTRUMENT B  "
                     strConsulta &= " WHERE A.INST_TYP=B.INST_TYP AND A.INST_TYP IN ( " & secType & ")"
                     strConsulta &= " AND B.STATUS<>'F' "
        

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function

          Public Shared Function ObtienerInstrumentEstaticosBatchValmer(ByVal numInstos As String ) As String 



        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT B.INST_NUM, B.INST_SH_NAM  "
                     strConsulta &= " FROM  INSTRUMENT B  "
                     strConsulta &= " WHERE B.INST_NUM in (" & numInstos & ")"
                     
        

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        If dtDatos.Rows.Count > 0 then 
           Return   If (IsDBNull (dtDatos.Rows(0)("INST_SH_NAM")),"",dtDatos.Rows(0)("INST_SH_NAM"))
        else
            Return ""
        End If
    
    End Function
          
          Public Shared Function ObtienerInfoTipoBatchValmer(ByVal numInstrumentos As String) As DataTable


        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = " SELECT INST_NUM, NOMINAL_RATE,RATE_TYP,RECV_IDX_COD,RECV_BASIS_FIXED,RECV_FREQ,RECV_MARGIN_ADD  "
                     strConsulta &= " FROM INSTRUMENT "
                     strConsulta &= " WHERE INST_NUM IN (" & numInstrumentos & ")"
                             

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function
                 
        #End Region
    
    #End Region

  #Region "Bench"

    
            Public Shared Function ObtenerDatosInstrumento() As DataTable

                Dim dbCnn As New CBaseDeDatosSyBase()
                Dim pm As New List(Of AseParameter)
                Dim dtDatos As New DataTable()
                Dim sMsgError As String = ""

                Dim strConsulta As String = "SELECT INST_ACR,INST_SH_NAM,INST_NUM FROM INSTRUMENT"

                dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

                If Not sMsgError.Equals("") Then
                    Throw New Exception(sMsgError)
                End If

                Return dtDatos



            End Function

    #End Region
  
  #Region "API Asset Allocation (AAA)"

        Public Shared Function ObtieneIDAPIDeudaRangos() As String
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT MAX(GEN_INST_NUM) FROM ID_INST_DEF WHERE GEN_INST_SH_NAM = 'DEUDA POR RANGOS GUB' AND GEN_INST_FLG = 'Y'"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos.Rows(0)(0).ToString()
    End Function
    
        Public Shared Function ObtieneCamposAPIDeudaRangos(ByVal strID As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = "SELECT FLD_ORDR, FLD_NAM_DEF, TABLE_DEF || '.' || FLD_ACR_DEF AS FIELD_NAM FROM ID_INST_DEF WHERE GEN_INST_NUM = " & strID & " ORDER BY FLD_ORDR"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function
            
        Public Shared Function ObtenerTiposValorAAA(ByVal tiposValor As String ) As DataTable
            Dim dbCnn As New CBaseDeDatosSyBase()
            Dim pm As New List(Of AseParameter)
            Dim dtDatos As New DataTable()
            Dim sMsgError As String = ""

            Dim strConsulta As String = ""


            strConsulta =  "SELECT  INST_TYP_ACR_LG FROM  ID_INST_TYP WHERE INST_TYP_ACR_LG  IN (" & tiposValor.ToString & ")" 
            dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDatos
        End Function


        Public Shared Function ObtenerContratosGXEAAA(ByVal contratos As String) As DataTable
            Dim dbCnn As New CBaseDeDatosSyBase()
            Dim pm As New List(Of AseParameter)
            Dim dtDatos As New DataTable()
            Dim sMsgError As String = ""

            Dim strConsulta As String = ""


            strConsulta =  "SELECT PF_COD FROM PORTFOLIO  WHERE PF_COD IN (" & contratos.ToString & ") and PF_C_FREE15 in ('Y','C')" 
            dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDatos
        End Function


        Public Shared Function ObtenerEstrategiasAAA() As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = ""


        strConsulta =  "SELECT CODIF_COD FROM ID_CODIF WHERE CODIF_GRP= 'STRATEGY'"
        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function



        Public Shared Function ObtenerInstrumentosAAA(ByVal instrumentos As string) As DataTable
       
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = ""

        strConsulta =  "SELECT RTRIM(LTRIM(INST_SH_NAM)) AS INST_SH_NAM  FROM INSTRUMENT  WHERE  MATURITY_DAT_END > getdate()"

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos

    End Function
     

        Public Shared Function ObtenerContratosAAA(ByVal contratos As String) As DataTable
            Dim dbCnn As New CBaseDeDatosSyBase()
            Dim pm As New List(Of AseParameter)
            Dim dtDatos As New DataTable()
            Dim sMsgError As String = ""

            Dim strConsulta As String = ""


            strConsulta =  "SELECT PF_COD FROM PORTFOLIO " 
            dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDatos
        End Function



        Public Shared Function ObtenerMesasAAA() As DataTable
            Dim dbCnn As New CBaseDeDatosSyBase()
            Dim pm As New List(Of AseParameter)
            Dim dtDatos As New DataTable()
            Dim sMsgError As String = ""

            Dim strConsulta As String = ""


            strConsulta =  "SELECT DESK_NAM FROM OMS_DESK_PROFILE"
            dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDatos
         End Function




    #End Region

  #Region "EXTRACCION FICHEROS"

    Public Shared Function ObtieneResultadoConsultas(ByVal queryArchivo As String) As DataTable
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)
        Dim dtDatos As New DataTable()
        Dim sMsgError As String = ""

        Dim strConsulta As String = queryArchivo

        dtDatos = dbCnn.EjecutarConsulta(strConsulta, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtDatos
    End Function

  #End Region

Shared Function ObtenerContratosAAA() As DataTable 
Throw New NotImplementedException 
 End Function 

End Class







